package com.yckj.finance.dao;

import com.yckj.finance.entity.Product;
import com.yckj.finance.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.ResultSet;
import java.util.*;

/**
 * ProductDao：包含增、删、改、查
 *
 * @author wuzhijun
 * @version 1.0
 */
public class ProductDao {
    /**
     * 添加实物分类
     * @param product 实物分类对象
     * @return 添加成功返回1，否则返回0
     */
    public int insertProduct(Product product) {
        int flag = 0;
        Connection conn = null;
        conn = DbUtil.getConnection();
        try {
            String sql = "INSERT INTO product(producttype,remark) VALUES(?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, product.getProducttype());
            pstmt.setString(2, product.getRemark());
            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn);
        }
        return flag;
    }

    /**
     * 查询所有实物分类
     * @return  List集合，包含查询到所有实物类别
     */
    public List<Product> selectProduct() {
        List<Product> pList = new ArrayList();
        Connection conn = null;
        conn = DbUtil.getConnection();

        String sql = "SELECT * FROM product";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Product pro = new Product();
                pro.setProductid(rs.getInt("productid"));
                pro.setProducttype(rs.getString("producttype"));
                pro.setRemark(rs.getString("remark"));
                pList.add(pro);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn);
        }
        return pList;
    }

    /**
     * 删除实物类别
     * @param productid 实物类别编号
     * @return 删除成功返回1，否则返回0
     */
    public int deleteProduct(int productid) {
        int flag = 0;
        Connection conn = null;
        conn = DbUtil.getConnection();
        try {
            String sql = "DELETE FROM product WHERE productid=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, productid);
            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn);
        }
        return flag;
    }

    /**
     * 修改实物类别
     * @param product 实物类别对象
     * @return 修改成功返回1，否则返回0
     */
    public int updateProduct(Product product) {
        int flag = 0;
        Connection conn = null;
        conn = DbUtil.getConnection();
        try {
            String sql = "UPDATE product SET producttype=?,remark=? WHERE productid=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, product.getProducttype());
            pstmt.setString(2, product.getRemark());
            pstmt.setInt(3, product.getProductid());
            flag = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn);
        }
        return flag;
    }

    /**
     * 按实物类别编号查询
     * @param productid
     * @return
     */
    public Product selectProductByid(int productid) {
        Product product = new Product();
        Connection conn = null;
        conn = DbUtil.getConnection();
        String sql = "SELECT * FROM product WHERE productid=?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, productid);
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            product.setProductid(rs.getInt("productid"));
            product.setProducttype(rs.getString("producttype"));
            product.setRemark(rs.getString("remark"));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn);
        }
        return product;
    }

    /**
     * 按类别名字查询
     * @param name 类别名称
     * @return 查询得到的集合List
     */
    public List<Product> queryProductList(String name) {
        List<Product> list = new ArrayList<Product>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DbUtil.getConnection();
            List<Object> params = new ArrayList<>();
            // 可变字符串，用来拼接SQL语句
            StringBuffer sb = new StringBuffer("SELECT * FROM product WHERE 1=1 ");
            if (name != null && !"".equals(name)) {
                sb.append("AND producttype LIKE ? ");
                pstmt = conn.prepareStatement(sb.toString());
                pstmt.setString(1, "%" + name + "%");
            } else {
                pstmt = conn.prepareStatement(sb.toString());
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Product product = new Product();
                product.setProductid(rs.getInt("productid"));
                product.setProducttype(rs.getString("producttype"));
                product.setRemark(rs.getString("remark"));
                list.add(product);
            }
        } catch (
                SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(conn);
        }
        return list;
    }
}
